﻿using KongMing.ORM.SqlSugar.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static KongMing.ORM.SqlSugar.SqlSugarHelper;

namespace KongMing.ORM.SqlSugar
{
    public class 子查询
    {
        public static void ChildQuery()
        {
            using (var db = GetDB())
            {
                //子查询 SqlFunc.Subqueryable
                var query_child = db.Queryable<Book>().Take(10).Select(x => new
                {
                    Sn = x.Sn,
                    BookName = x.Title,
                    Author = x.Author,
                    CreateUserName = SqlFunc.Subqueryable<User>().Where(u => u.Id == x.CreateUserId).Select(x => x.Name),
                    CreateDate = SqlFunc.GetDate().ToString("yyyy-MM-dd")
                }).OrderBy(x => x.Sn, OrderByType.Desc).ToList();

                //Exists 
                //等同于sql：select * from book x where exists(select * from [user] u where u.name=x.author)
                var query_exists = db.Queryable<Book>().Where(x => SqlFunc.Subqueryable<User>().Where(u => u.Name == x.Author).Any()).ToList();
            }
        }
    }
}
